package com.zsk.parse.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 解析excel表格 https://blog.csdn.net/qq_34474324/article/details/97627474
 * 
 * @author zincredible
 * @date 2020/03/11 11:15:02
 */

// <dependency>
// <groupId>org.apache.poi</groupId>
// <artifactId>poi-ooxml</artifactId>
// <version>3.17</version>
// </dependency>
public class ParseExcel {

    public final static String XLS = ".xls";
    public final static String XLSX = ".xlsx";

    public static Workbook getWorkbook(String filePath) {
        Workbook wb = null;
        if (filePath == null || !new File(filePath).isFile())
            return null;
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (XLS.equals(extString))
                return wb = new HSSFWorkbook(is);
            else if (XLSX.equals(extString))
                return wb = new XSSFWorkbook(is);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return wb;
    }

    public static Object getCellValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            // 判断cell类型
            CellType cellType = cell.getCellTypeEnum();
            switch (cellType) {
                case STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                case NUMERIC: {
                    if (DateUtil.isCellDateFormatted(cell))// 判断cell是否为日期格式
                        cellValue = cell.getDateCellValue();// 转换为日期格式YYYY-mm-dd
                    else
                        cellValue = String.valueOf(cell.getNumericCellValue());// 数字
                    break;
                }
                case BOOLEAN: {
                    cellValue = cell.getBooleanCellValue();
                    break;
                }
                case FORMULA: {
                    cellValue = cell.getCellFormula();
                    break;
                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }

    public static void main(String[] args) {
        String filePath = "F:\\DLP组资料\\APP开发资料\\3D云APP开发任务列表.xlsx";
        Workbook wb = getWorkbook(filePath);
        if (wb != null) {
            // 获取第一个sheet
            Sheet sheet = wb.getSheetAt(0);
            // 获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            // 获取第一行
            Row row = sheet.getRow(0);
            // 获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            for (int i = 0; i < rownum; i++) {
                row = sheet.getRow(i);
                if (row == null)
                    continue;
                for (int j = 0; j <= colnum; j++) {
                    String cellValue = getCellValue(row.getCell(j)).toString();
                    System.out.print(cellValue + "\t");
                }
                System.out.println();
            }
        }

    }
}
